package frontend;

import java.sql.SQLException;

import java.sql.*;
import java.util.ArrayList;
import java.util.Dictionary;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;

import backend.Location;
import backend.Warehouse;

public class Analyzer {

	/**
	 * @param args
	 */
	public static void main(String[] args)
	{
		Warehouse wh = new Warehouse();
		ResultSet res;
		
		try
		{
			// Warehouse setup
			wh.open();
			Connection sqlCon = wh.getSqlCon();
			Statement s = sqlCon.createStatement();
			
			// Question: 
			
			res = s.executeQuery(
					"SELECT COUNT(DISTINCT f.bag_ID) as number_of_bags, l.l_airport as airport, d.d_month as month " +  
					"from fact_stay f " +
					"JOIN dimension_location l " +
						"ON f.location_ID = l.l_ID " +
					"JOIN dimension_date d " +
						"ON f.date_ID = d.d_ID " +
					"GROUP BY l.l_airport, d.d_month WITH ROLLUP;"
					);
			
			//System.out.println("Number of bags handled in different airports by months:");
			
			// Question: average time spent by bags on location-type?
			res = s.executeQuery(
					"SELECT l.l_location_type, AVG(f.duration) AS avgduration " +
					"FROM dimension_location l " +
					"JOIN fact_stay f " +
						"ON l.l_ID = f.location_ID " +
					"GROUP BY l.l_location_type WITH ROLLUP");
			
			System.out.println("Average time spent by bags in location types:");
			System.out.println("-----------------------------------\nLocation Type      Average Duration\n-----------------------------------");
			while (res.next())
			{
				String type = res.getString("l.l_location_type");
				if (type == null)
				{
					System.out.println("-----------------------------------");
					type = "ALL";
				}
				System.out.println(padRight(type, 10) + padLeft(String.valueOf(res.getDouble("avgduration")), 25));
			}
			
			// Drill-down: average time spent by bags on location-type, by airport?
			res = s.executeQuery(
					"SELECT l.l_location_type, l.l_airport, AVG(f.duration) AS avgduration " +
					"FROM dimension_location l " +
					"JOIN fact_stay f " +
						"ON l.l_ID = f.location_ID " +
					"GROUP BY l.l_airport, l.l_location_type");

			System.out.println("-----------------------------------");
			System.out.println("Average time spent by bags in location types, per airport:");
			System.out.println("-----------------------------------");
			
			HashMap<String,HashMap<String,Double>> table = new HashMap<String,HashMap<String,Double>>();
			HashSet<String> airports = new HashSet<String>();
			
			String curAir = "";
			String curType = "";
			while (res.next())
			{
				if (res.getString("l.l_airport") != curAir)
				{
					curAir = res.getString("l.l_airport");
					airports.add(curAir);
				}
				curType = res.getString("l.l_location_type");
				
				if (table.get(curType) == null)
				{
					table.put(curType, new HashMap<String,Double>());
				}
				
				table.get(curType).put(curAir, res.getDouble("avgduration"));
				//System.out.println(padRight(res.getString("l.l_airport"), 10) + padRight(res.getString("l.l_location_type"), 10) + padLeft(String.valueOf(res.getDouble("avgduration")), 25));
			}
			
			// Find distinct airports
			
			String output = "";

			output += padRight("Avg.Dur.", 10);
			for (String air : airports)
			{
				output += padLeft(air, 8);
			}
			int outLen = output.length();
			output += "\n";
			for (int i=0; i<outLen; i++) output += "-";
			output += "\n";
			
			for (String type : table.keySet())
			{
				output += padRight(type, 10);
				for (String air : airports)
				{
					Double avg = table.get(type).get(air);
					if (avg == null)
						avg = 0.0;
					output += padLeft(String.format("%.2f", avg), 8);
				}
				
				System.out.println(output);
				output = "";
			}
			
			
			// Question: how many bags are lost per airport?
			/*res = s.executeQuery(
					"SELECT l.l_airport " +
					"FROM dimension_location l " +
					"JOIN fact_stay f " +
						"ON l.l_ID = f.location_ID " +
					"GROUP BY l.l_airport, l.l_location_type");
			*/
			
			
			res.close();
			s.close();
			
			wh.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	public static String padRight(String s, int n) {
	     return String.format("%1$-" + n + "s", s);  
	}

	public static String padLeft(String s, int n) {
	    return String.format("%1$#" + n + "s", s);  
	}

}
